using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using MyClever.Lib;

namespace MyClever.DefaultPlugins
{
    public class ReadSQLite : MissionPlugin
    {
        [Package(Direction.Input, "Defines the setting to read from the SQLite database file.")]
        enum SQLightDBFile
        {
            [Column(typeof(string), "The SQLite database file, you like read from.")]
            FilePath,
            [Column(typeof(string), "The table name, you like to read.")]
            TableName,
            [Column(typeof(string), "Which data you like to read. (example: column1 > 12)",
                DefaultValue="1")]
            Where,
            [Column(typeof(bool), "If true, all read data will be delete!",
                DefaultValue="False")]
            DeleteReadData
        }

        [Package(Direction.Output, "Defines the data, which was loaded from the database.")]
        enum DBData { }

        [Plugin(PluginDevStatus.Production, "Reads data from your favorite table in the expected SQLite database file.")]
        public ReadSQLite()
        {
        }

        public override void Initialize()
        {
            
            //This is the first Function called by the host...
            //Put anything needed to start with here first
        }

        public override void Dispose()
        {
            //Put any cleanup code in here for when the program is stopped
        }

        /// <summary>
        /// Generate new Source-content.
        /// </summary>
        protected override void Work()
        {            
            DataTableReader sqliteDBPackage = this.GetDataReader(typeof(SQLightDBFile));
            while (sqliteDBPackage.Read())
            {
                this.AddNewLoggingMessage(typeof(SQLightDBFile), String.Format("read table: '{0}' from file: {1}", sqliteDBPackage[SQLightDBFile.TableName.ToString()].ToString(), sqliteDBPackage[SQLightDBFile.FilePath.ToString()].ToString()));

                SQLiteConnection connection = new SQLiteConnection();
                connection.ConnectionString = "Data Source=" + sqliteDBPackage[SQLightDBFile.FilePath.ToString()].ToString();
                connection.Open();

                SQLiteCommand command = new SQLiteCommand(connection);
                command = new SQLiteCommand(connection);
                command.CommandText = String.Format("SELECT * FROM {0} WHERE {1}", sqliteDBPackage[SQLightDBFile.TableName.ToString()].ToString(), sqliteDBPackage[SQLightDBFile.Where.ToString()].ToString());

                SQLiteDataReader reader = command.ExecuteReader();

                // create schema for output package 'DBData'
                DataTable schemaTable = reader.GetSchemaTable();
                List<KeyValuePair<string, Type>> columnDefinitions = new List<KeyValuePair<string, Type>>();
                foreach (DataRow row in schemaTable.Rows)
	            {
                    columnDefinitions.Add(new KeyValuePair<string, Type>(row["ColumnName"].ToString(), (Type)row["DataType"]));
                }
                this.CreateNewSchemaForPackage(typeof(DBData), columnDefinitions);

                while (reader.Read())
                {
                    DataRow newRow = this.CreateNewRowFromPackage(typeof(DBData));
                    for (int columnID = 0; columnID < reader.FieldCount; columnID++)
                    {
                        newRow[columnID] = reader[columnID];
                    }
                    this.AddNewRowToPackage(typeof(DBData), newRow);

                    // delete read data, if required
                    if (true == Convert.ToBoolean(sqliteDBPackage[SQLightDBFile.DeleteReadData.ToString()]))
                    {
                        string columnValuesQuery = "";
                        foreach (KeyValuePair<string, Type> columnDefinitionOn in columnDefinitions)
                        {
                            columnValuesQuery += " AND " + columnDefinitionOn.Key + " = '" + newRow[columnDefinitionOn.Key].ToString().Replace("'", "''") + "'";
                        }
                        string deleteCommandQuery = "DELETE FROM " + sqliteDBPackage[SQLightDBFile.TableName.ToString()].ToString() + " WHERE " + columnValuesQuery.Substring(4);

                        SQLiteCommand deleteCommand = new SQLiteCommand(connection);
                        deleteCommand.CommandText = deleteCommandQuery;
                        deleteCommand.ExecuteNonQuery();
                        deleteCommand.Dispose();
                    }
                }

                reader.Dispose();
                reader.Close();

                command.Dispose();

                connection.Close();
                connection.Dispose();
            }            
        }
    }
}
